package edu.bw.dao.impl;

import edu.bw.dao.DeptDao;
import edu.bw.dto.SelectDeptByConditionForm;
import edu.bw.dto.UpdateDeptForm;
import edu.bw.pojo.Dept;
import edu.bw.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class DeptDaoImpl implements DeptDao {
    QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    /**
     * SELECT d.id,d.dept_name AS deptName,d.tel,d.email,d.`desc`,COUNT(u.id) AS emps
     * FROM bw_zzy_dept d LEFT JOIN bw_zzy_users u ON d.id = u.dept_id AND u.`status`=1 #查询在职状态的员工
     * WHERE 1 = 1
     * AND d.dept_name LIKE '%后%'
     * GROUP BY d.id
     * LIMIT 0,10
     * @param selectCondition 查询相关的参数
     * @return
     */
    @Override
    public List<Map<String, Object>> selectDeptByCondition(SelectDeptByConditionForm selectCondition) {
        //StringBuilder专门用来拼接sql
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT d.id,d.dept_name AS deptName,d.tel,d.email,d.`desc`,COUNT(u.id) AS emps  FROM bw_zzy_dept d " +
                "LEFT JOIN bw_zzy_users u ON d.id = u.dept_id AND u.`status`=1 " +
                "WHERE 1 = 1 ");
        //queryParams用来设置?参数值
        List<Object> queryParams = new ArrayList<>();
        if (selectCondition.getDeptName() != null && !selectCondition.getDeptName().isEmpty()) {
            //此时拼接部门模糊查询
            sql.append(" AND d.dept_name LIKE ? ");
            queryParams.add("%" + selectCondition.getDeptName() + "%");
        }

        //拼接剩余sql语句
        sql.append(" GROUP BY d.id LIMIT ?,? ");
        queryParams.add((selectCondition.getPage() - 1) * selectCondition.getLength());
        queryParams.add(selectCondition.getLength());

        try {
            return qr.query(sql.toString(), new MapListHandler(), queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * SELECT COUNT(*)
     * FROM (SELECT d.id
     *       FROM bw_zzy_dept d
     *                LEFT JOIN bw_zzy_users u ON d.id = u.dept_id
     *           AND u.`status` = 1 #查询在职状态的员工
     *       WHERE 1 = 1
     *       AND d.dept_name LIKE '%后%'
     *       GROUP BY d.id) AS temp
     * @param selectCondition 查询相关的参数
     * @return
     */
    @Override
    public Long SelectDeptByConditionCount(SelectDeptByConditionForm selectCondition) {
        //StringBuilder专门用来拼接sql
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) FROM (SELECT d.id FROM bw_zzy_dept d " +
                "LEFT JOIN bw_zzy_users u ON d.id = u.dept_id " +
                "AND u.`status` = 1 WHERE 1 = 1 ");

        //queryParams用来设置?参数值
        List<Object> queryParams = new ArrayList<>();
        if (selectCondition.getDeptName() != null && !selectCondition.getDeptName().isEmpty()) {
            //此时拼接部门模糊查询
            sql.append(" AND d.dept_name LIKE ? ");
            queryParams.add("%" + selectCondition.getDeptName() + "%");
        }

        sql.append(" GROUP BY d.id) AS temp");

        //最终查询
        try {
            return qr.query(sql.toString(), new ScalarHandler<>(), queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer insert(Dept dept) {
        try {
            return qr.update("INSERT INTO bw_zzy_dept SET dept_name = ?,tel = ?,email = ?,`desc` = ?",
                    dept.getDeptName(), dept.getTel(), dept.getEmail(), dept.getDesc());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> selectById(Integer id) {
        try {
            return qr.query("SELECT id, dept_name AS deptName, tel, email, `desc` FROM bw_zzy_dept WHERE id = ?"
                    , new MapListHandler()
                    , id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer update(UpdateDeptForm updateDeptForm) {
        try {
            return qr.update("UPDATE bw_zzy_dept SET dept_name=?, tel=?, email=?,`desc`=? WHERE id = ?",
                    updateDeptForm.getDeptName(),
                    updateDeptForm.getTel(),
                    updateDeptForm.getEmail(),
                    updateDeptForm.getDesc(),
                    updateDeptForm.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Boolean selectCanDelete(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("SELECT IF(SUM(temp.users) > 0, FALSE , TRUE) AS result FROM ( " );
        sql.append(" SELECT COUNT(u.id) AS users FROM bw_zzy_dept d INNER JOIN  bw_zzy_users u ON d.id = u.dept_id WHERE d.id IN ( ")
                .append(ids.stream().map(id -> "?").collect(Collectors.joining(", ")))
                .append(" ) GROUP BY d.id ) temp");

        try {
            Long result = qr.query(sql.toString(), new ScalarHandler<Long>(), ids.toArray());
            return result == 1 ? true : false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int deleteDeptByIds(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM bw_zzy_dept WHERE id in ( ");
        sql.append(ids.stream().map(id -> "?").collect(Collectors.joining(", ")))
                .append(")");

        try {
            return qr.update(sql.toString(), ids.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> selectAllDept() {
        try {
            return qr.query("SELECT id,dept_name AS deptName " +
                    "  FROM bw_zzy_dept " +
                    "  ORDER BY id ", new MapListHandler());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
